package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.PrinterMapper;
import ch.zbw.pra.sysventory.model.Printer;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Printer) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben, lesen und loeschen
 * 
 * @author Roberto Lucas
 */

public class PrinterJDBCTemplate implements Serializable {
	
	private static final long serialVersionUID = -3371142649670518487L;
	private static Logger LOG = LoggerFactory.getLogger(PrinterJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Printer (prinName, prinDriverName,prinVersion, prin_compID_fk)" +
			" VALUES(?,?,?,?)";

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	
	public Integer insertPrinter(final Printer printer, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();	
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, printer.getNamePrinter()); 
					ps.setString(2, printer.getDriverNamePrinter()); 
					ps.setString(3, printer.getDriverVersionPrinter());
					ps.setInt(4, computerId);
					return ps; 
				} 
			}, keyHolder);
		} catch (DataAccessException e){
			LOG.debug("Failed to insert Printer for ComputerId "+ computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted Printer with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}

	public List<Printer> listPrinters() {
		String SQL = "SELECT * FROM Printer GROUP BY prinName,prinDriverName,prinVersion";;
		List<Printer> printers = new ArrayList<Printer>();
		try{
			printers = jdbcTemplateObject.query(SQL,
						new PrinterMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Printers");
			LOG.debug(e.toString());
		}
		LOG.debug("Loaded a list of Printers");
		return printers;
	}
	
	public Printer readPrinterById(Integer printerId){
		String SQL = "SELECT * FROM Printer WHERE prinID_pk = ?";
	    Printer printer = new Printer();
	    try{
	    	printer = jdbcTemplateObject.queryForObject(SQL, 
	                        new Object[]{printerId}, new PrinterMapper());
	    } catch (DataAccessException e){
	    	LOG.debug("Failed to read Printer with id "+printerId);
	    	LOG.error(e.toString());
	    }
	    LOG.debug("Read Printer with Id = "+ printer.getPrinterId());
	    return printer;
	}
	
	public void deletePrinterById(Integer printerId) {
		String SQL = "DELETE FROM Printer WHERE prinID_pk = ?";
		try{
			jdbcTemplateObject.update(SQL, printerId);
		} catch (DataAccessException e){
			LOG.debug("Failed to delete Printer with id "+ printerId);
			LOG.error(e.toString());
		}
		LOG.debug("Deleted Processor with id = " + printerId);
	}

	public List<Printer> listAllPrintersFromComputer(Integer computerId) {
		String SQL = "SELECT * FROM Printer WHERE prin_compID_fk = ?";
		List<Printer> printers = new ArrayList<Printer>();
		try{
			printers = jdbcTemplateObject.query(SQL, new Object[]{computerId}, 
					new PrinterMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a List of Printers for ComputerID "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Printers for ComputerId "+computerId);
	    return printers;
	}

}